"""add_account_money_extend_unique_constraint

Revision ID: 012_account_money_extend_unique
Revises: 011_system_integration_fields
Create Date: 2025-10-21 18:00:00.000000

"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy.engine.reflection import Inspector

# revision identifiers, used by Alembic.
revision = '012_account_money_extend_unique'
down_revision = '011_system_integration_fields'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    conn = op.get_bind()
    inspector = Inspector.from_engine(conn)
    tables = inspector.get_table_names()
    
    if 'account_money_extend' in tables:
        # 首先删除重复数据，只保留每个account_id中updated_at最大的记录
        conn.execute(sa.text("""
            DELETE FROM account_money_extend 
            WHERE id NOT IN (
                SELECT DISTINCT ON (account_id) id 
                FROM account_money_extend 
                ORDER BY account_id, updated_at DESC
            )
        """))
        
        # 删除现有的普通索引
        with op.batch_alter_table('account_money_extend', schema=None) as batch_op:
            try:
                batch_op.drop_index('idx_account_money_account_id')
            except Exception:
                # 如果索引不存在，忽略错误
                pass
        
        # 创建唯一约束
        with op.batch_alter_table('account_money_extend', schema=None) as batch_op:
            batch_op.create_unique_constraint('idx_account_money_account_id_unique', ['account_id'])
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    if 'account_money_extend' in tables:
        with op.batch_alter_table('account_money_extend', schema=None) as batch_op:
            try:
                batch_op.drop_constraint('idx_account_money_account_id_unique', type_='unique')
            except Exception:
                # 如果约束不存在，忽略错误
                pass
            
            # 重新创建普通索引
            batch_op.create_index('idx_account_money_account_id', ['account_id'], unique=False)
    # ### end Alembic commands ###
